import re
from copy import deepcopy

import requests
import sqlparse
from treelib import Tree
from sqlparse.sql import Identifier, IdentifierList
class ParseTree:

    mapping_star_to_table = []
    star_dict = {}
    ip = "http://22.22.30.13:9080" #元数据接口
    nebulaApiToken = "sdfsafsafsafersgdkjlfasdf"
    url = ip + "/test/tableHasFields"
    headers = {"nebula-api-token":nebulaApiToken}

    def __int__(self,sql_schema):
        self.sql_schema = sql_schema

    def parseTree(self,tree):

        for node in tree.expand_tree(mode=Tree.DEPTH, sorting=False):
            table_object = tree[node].data
            # if table_object:
            #     print('have_star:'+str(table_object.have_star)) #是否有星
            #     print('subquery_alias:' + str(table_object.subquery_alias))  # 子查询别名
            #     print('level:'+str(table_object.level))
            #     print('column:'+str(table_object.column)) #字段
            #     print('index_num:'+str(table_object.index_num)) #型号在字段中出现的位置
            #     print('star_seq:'+str(table_object.star_seq)) #第几次出现的星号
            #     print('table:'+str(table_object.table)) #表
            #     print('----------------')

            if table_object:
                if table_object.have_star == 'yes':
                    parsed = sqlparse.parse(table_object.column)[0]
                    if len(parsed.value) > 1 and ',' in parsed.value: #处理多个字段 20231107 add (and ',' in parsed.value)
                        #映射星号和对应表or子查询的关系
                        col_list = []
                        for i in parsed[0]:
                            if i.is_group:
                                col_list.append(i)

                        if col_list[table_object.index_num].value == '*':
                            for table in table_object.table:
                                if table.split('%%')[0] != 'subquery' and table.split('%%')[1] != 'un_alias':
                                    pass #取表字段
                        else:
                            for table in table_object.table:
                                if table.split('%%')[0] == 'subquery' and table.split('%%')[1].upper() == col_list[table_object.index_num].value.split('.')[0].upper():
                                    self.star_dict['subquery_alias'] = table_object.subquery_alias
                                    self.star_dict['star_col'] = col_list[table_object.index_num].value
                                    self.star_dict['rel_col'] = ''
                                    self.star_dict['seq'] = table_object.seq
                                    self.mapping_star_to_table.append(deepcopy(self.star_dict))
                                    self.star_dict.clear()

                                elif table.split('%%')[0] != 'subquery' and table.split('%%')[1] != 'un_alias' and table.split('%%')[1].upper() == col_list[table_object.index_num].value.split('.')[0].upper():
                                    self.star_dict['subquery_alias'] = table_object.subquery_alias
                                    self.star_dict['star_col'] = col_list[table_object.index_num].value
                                    self.star_dict['seq'] = table_object.seq

                                    tableNameUp = table.split('%%')[0]
                                    if '.' not in tableNameUp:
                                        tableNameUp = self.sql_schema + '.' + tableNameUp
                                    res_cols = requests.post(self.url,headers=self.headers,json={"tableNameUp":tableNameUp}).text

                                    res_cols_dict = eval(res_cols)
                                    if res_cols_dict.get('code') == 200 and res_cols_dict.get('data') != '':
                                        self.star_dict['rel_col'] = res_cols_dict.get('data')
                                    elif res_cols_dict.get('code') != 200:
                                        print('访问元数据接口失败')
                                    elif res_cols_dict.get('code') == 200 and res_cols_dict.get('data') == '':
                                        print('元数据缺失表:' + str(table))

                                    self.mapping_star_to_table.append(deepcopy(self.star_dict))
                                    self.star_dict.clear()
                    elif len(parsed.value.strip()) == 1:
                        #处理*
                        if len(table_object.table) == 1 and table_object.table[0].split('%%')[0] != 'subquery' :
                            self.star_dict['subquery_alias'] =  table_object.subquery_alias
                            self.star_dict['star_col'] = table_object.column
                            self.star_dict['seq'] = table_object.seq

                            tableNameUp = table_object.table[0].split('%%')[0]
                            if '.' not in tableNameUp:
                                tableNameUp = self.sql_schema + '.' + tableNameUp
                            res_cols = requests.post(self.url, headers=self.headers,
                                                     json={"tableNameUp": tableNameUp}).text

                            res_cols_dict = eval(res_cols)
                            if res_cols_dict.get('code') == 200 and res_cols_dict.get('data') != '':
                                self.star_dict['rel_col'] = res_cols_dict.get('data')
                            elif res_cols_dict.get('code') != 200:
                                print('访问元数据接口失败')
                            elif res_cols_dict.get('code') == 200 and res_cols_dict.get('data') == '':
                                print('元数据缺失表:' + str(table_object.table[0]))

                            self.mapping_star_to_table.append(deepcopy(self.star_dict))
                            self.star_dict.clear()
                    else:
                        #处理t.*  20231107
                        for table in table_object.table:
                            if table.split('%%')[0] != 'subquery' and table.split('%%')[1].upper() == parsed.value.split('.')[0].upper():
                                self.star_dict['subquery_alias'] = table_object.subquery_alias
                                self.star_dict['star_col'] = parsed.value
                                self.star_dict['seq'] = table_object.seq

                                tableNameUp = table.split('%%')[0]
                                if '.' not in tableNameUp:
                                    tableNameUp = self.sql_schema + '.' + tableNameUp
                                res_cols = requests.post(self.url, headers=self.headers,
                                                         json={"tableNameUp": tableNameUp}).text

                                res_cols_dict = eval(res_cols)
                                if res_cols_dict.get('code') == 200 and res_cols_dict.get('data') != '':
                                    self.star_dict['rel_col'] = res_cols_dict.get('data')
                                elif res_cols_dict.get('code') != 200:
                                    print('访问元数据接口失败')
                                elif res_cols_dict.get('code') == 200 and res_cols_dict.get('data') == '':
                                    print('元数据缺失表:' + str(table))

                                self.mapping_star_to_table.append(deepcopy(self.star_dict))
                                self.star_dict.clear()
                            elif table.split('%%')[0] != 'subquery' and table.split('%%')[1] != 'un_alias':
                                self.star_dict['subquery_alias'] = table_object.subquery_alias
                                self.star_dict['star_col'] = parsed.value
                                self.star_dict['seq'] = table_object.seq
                                self.star_dict['rel_col'] = ''
                                self.mapping_star_to_table.append(deepcopy(self.star_dict))
                                self.star_dict.clear()

                #查询上一步映射的关系，将子查询的字段替换映射列表中rel_col的值
                if  len(self.mapping_star_to_table) > 0:
                    for k,v in enumerate(self.mapping_star_to_table):
                        if table_object.subquery_alias != 'unkown' and '.' in v.get('star_col'):
                            if v.get('star_col').split('.')[0] == table_object.subquery_alias:
                                if table_object.have_star == 'no':
                                    col_list_tmp = []
                                    for x in sqlparse.parse(table_object.column)[0]:
                                        for key,i in enumerate(x):
                                            if 'AS' in i.value.upper():
                                                col_list_tmp.append(i.value.upper().strip().split(' AS ')[-1])
                                            elif re.match('.*(ROW_NUMBER\(\)|RANK\(\)|DENSE_RANK\(\)|LEAD\(\)|LAG\(\)|MAX\(\)|MIN\(\)|FIRST_VALUE\(\)|LAST_VALUE\(\)).*',i.value.upper()) is not None and re.match('.*OVER.*',i.value.upper()) is None:
                                                continue
                                            elif 'CASE WHEN' in i.value.upper():
                                                col_list_tmp.append('CASEWHEN' + str(key))  #CASE WHEN？？？？？？？？？？？？？
                                            elif '.' in i.value.strip().upper() and len(i.value.upper().split('.')) == 2 and '*' not in i.value:
                                                col_list_tmp.append(i.value.upper().strip().split('.')[-1])
                                            elif re.match('[A-Z]*',i.value.strip().upper()).group() != '':
                                                col_list_tmp.append(i.value.upper())
                                    self.mapping_star_to_table[k]['rel_col'] = ','.join(col_list_tmp)
                                else:
                                    self.mapping_star_to_table[k]['rel_col'] = self.mapping_star_to_table[-1].get('rel_col')


                        if self.mapping_star_to_table[k].get('star_col') == '*' and self.mapping_star_to_table[k].get('rel_col') == '' and table_object.have_star == 'no' and self.mapping_star_to_table[k].get('seq') == table_object.seq -1 :
                            col_list_tmp = []
                            for x in sqlparse.parse(table_object.column)[0]:
                                if x.value != ' ':
                                    for key,i in enumerate(x):
                                        if ' AS ' in i.value.upper():
                                            col_list_tmp.append(i.value.upper().strip().split(' AS ')[-1])
                                        elif re.match(
                                                '.*(ROW_NUMBER\(\)|RANK\(\)|DENSE_RANK\(\)|LEAD\(\)|LAG\(\)|MAX\(\)|MIN\(\)|FIRST_VALUE\(\)|LAST_VALUE\(\)).*',
                                                i.value.upper()) is not None and re.match('.*OVER.*',
                                                                                          i.value.upper()) is None:
                                            continue
                                        elif 'CASE WHEN' in i.value.upper():
                                            col_list_tmp.append('CASEWHEN' + str(key))  # CASE WHEN？？？？？？？？？？？？？
                                        elif '.' in i.value.upper() and len(
                                                i.value.strip().upper().split('.')) == 2 and '*' not in i.value:
                                            col_list_tmp.append(i.value.upper().strip().split('.')[-1])
                                        elif re.match('[A-Z]*', i.value.strip().upper()).group() != '':
                                            col_list_tmp.append(i.value.upper())
                            self.mapping_star_to_table[k]['rel_col'] = ','.join(col_list_tmp)

        for k,v in enumerate(self.mapping_star_to_table):
            if v.get('star_col') == '*' and v.get('rel_col') == '' and len(self.mapping_star_to_table) - 1 > k:
                self.mapping_star_to_table[k]['rel_col'] = self.mapping_star_to_table[k+1]['rel_col']


        print(self.mapping_star_to_table)

        final_list = []
        for dict in self.mapping_star_to_table:
            if len(dict.get('star_col')) > 1:
                tmp_list = []
                for col in dict.get('rel_col').split(','):
                    if len(col.split('.')) == 1:
                        tmp_list.append(dict.get('star_col').split('.')[0] + str('.') + col)
                    else:
                        tmp_list.append(dict.get('star_col').split('.')[0] + str('.') + col.split('.')[1])
                final_list.append(tmp_list)
            else:
                final_list.append([dict.get('rel_col')])

        # print(final_list)

        return final_list